-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Insert]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Update]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Delete]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Load]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectAll]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectBy_HoSo_ID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectBy_HoSo_ID]

IF OBJECT_ID(N'[dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteBy_HoSo_ID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteBy_HoSo_ID]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Insert]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Insert]
	@HoSo_ID bigint,
	@Barcode varchar(20),
	@LuongTonDau decimal(18, 2),
	@SoLuongNhap decimal(18, 2),
	@SoLuongXuat decimal(18, 2),
	@LuongTonCuoi decimal(18, 2),
	@DoanhThu decimal(18, 0),
	@TyTrong decimal(18, 4),
	@ID bigint OUTPUT
AS

INSERT INTO [dbo].[t_WM_ThanhKhoan_HoSoChiTiet]
(
	[HoSo_ID],
	[Barcode],
	[LuongTonDau],
	[SoLuongNhap],
	[SoLuongXuat],
	[LuongTonCuoi],
	[DoanhThu],
	[TyTrong]
)
VALUES 
(
	@HoSo_ID,
	@Barcode,
	@LuongTonDau,
	@SoLuongNhap,
	@SoLuongXuat,
	@LuongTonCuoi,
	@DoanhThu,
	@TyTrong
)

SET @ID = SCOPE_IDENTITY()

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Update]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Update]
	@ID bigint,
	@HoSo_ID bigint,
	@Barcode varchar(20),
	@LuongTonDau decimal(18, 2),
	@SoLuongNhap decimal(18, 2),
	@SoLuongXuat decimal(18, 2),
	@LuongTonCuoi decimal(18, 2),
	@DoanhThu decimal(18, 0),
	@TyTrong decimal(18, 4)
AS

UPDATE
	[dbo].[t_WM_ThanhKhoan_HoSoChiTiet]
SET
	[HoSo_ID] = @HoSo_ID,
	[Barcode] = @Barcode,
	[LuongTonDau] = @LuongTonDau,
	[SoLuongNhap] = @SoLuongNhap,
	[SoLuongXuat] = @SoLuongXuat,
	[LuongTonCuoi] = @LuongTonCuoi,
	[DoanhThu] = @DoanhThu,
	[TyTrong] = @TyTrong
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_InsertUpdate]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_InsertUpdate]
	@ID bigint,
	@HoSo_ID bigint,
	@Barcode varchar(20),
	@LuongTonDau decimal(18, 2),
	@SoLuongNhap decimal(18, 2),
	@SoLuongXuat decimal(18, 2),
	@LuongTonCuoi decimal(18, 2),
	@DoanhThu decimal(18, 0),
	@TyTrong decimal(18, 4)
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_WM_ThanhKhoan_HoSoChiTiet] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_WM_ThanhKhoan_HoSoChiTiet] 
		SET
			[HoSo_ID] = @HoSo_ID,
			[Barcode] = @Barcode,
			[LuongTonDau] = @LuongTonDau,
			[SoLuongNhap] = @SoLuongNhap,
			[SoLuongXuat] = @SoLuongXuat,
			[LuongTonCuoi] = @LuongTonCuoi,
			[DoanhThu] = @DoanhThu,
			[TyTrong] = @TyTrong
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_WM_ThanhKhoan_HoSoChiTiet]
		(
			[HoSo_ID],
			[Barcode],
			[LuongTonDau],
			[SoLuongNhap],
			[SoLuongXuat],
			[LuongTonCuoi],
			[DoanhThu],
			[TyTrong]
		)
		VALUES 
		(
			@HoSo_ID,
			@Barcode,
			@LuongTonDau,
			@SoLuongNhap,
			@SoLuongXuat,
			@LuongTonCuoi,
			@DoanhThu,
			@TyTrong
		)		
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Delete]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Delete]
	@ID bigint
AS

DELETE FROM 
	[dbo].[t_WM_ThanhKhoan_HoSoChiTiet]
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteBy_HoSo_ID]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteBy_HoSo_ID]
	@HoSo_ID bigint
AS

DELETE FROM [dbo].[t_WM_ThanhKhoan_HoSoChiTiet]
WHERE
	[HoSo_ID] = @HoSo_ID

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_DeleteDynamic]
	@WhereCondition NVARCHAR(500)
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DELETE FROM [dbo].[t_WM_ThanhKhoan_HoSoChiTiet] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Load]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_Load]
	@ID bigint
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[HoSo_ID],
	[Barcode],
	[LuongTonDau],
	[SoLuongNhap],
	[SoLuongXuat],
	[LuongTonCuoi],
	[DoanhThu],
	[TyTrong]
FROM
	[dbo].[t_WM_ThanhKhoan_HoSoChiTiet]
WHERE
	[ID] = @ID
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectBy_HoSo_ID]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectBy_HoSo_ID]
	@HoSo_ID bigint
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[HoSo_ID],
	[Barcode],
	[LuongTonDau],
	[SoLuongNhap],
	[SoLuongXuat],
	[LuongTonCuoi],
	[DoanhThu],
	[TyTrong]
FROM
	[dbo].[t_WM_ThanhKhoan_HoSoChiTiet]
WHERE
	[HoSo_ID] = @HoSo_ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectDynamic]
	@WhereCondition NVARCHAR(500),
	@OrderByExpression NVARCHAR(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'SELECT 
	[ID],
	[HoSo_ID],
	[Barcode],
	[LuongTonDau],
	[SoLuongNhap],
	[SoLuongXuat],
	[LuongTonCuoi],
	[DoanhThu],
	[TyTrong]
FROM [dbo].[t_WM_ThanhKhoan_HoSoChiTiet] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectAll]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, October 09, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_ThanhKhoan_HoSoChiTiet_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[HoSo_ID],
	[Barcode],
	[LuongTonDau],
	[SoLuongNhap],
	[SoLuongXuat],
	[LuongTonCuoi],
	[DoanhThu],
	[TyTrong]
FROM
	[dbo].[t_WM_ThanhKhoan_HoSoChiTiet]	

GO

